package com.zzn.bookManage.dao.impl;

import com.zzn.bookManage.dao.BookDao;
import com.zzn.bookManage.exceptions.MyException;
import com.zzn.bookManage.pojo.Book;
import com.zzn.bookManage.util.DBUtil;
import com.zzn.bookManage.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class BookDaoImpl implements BookDao {

    private final Connection conn = DBUtil.getConnection();

    @Override
    public List<Book> listBooks(int limit, int offset) {
        try {
            String sql = "select *" +
                    "from `books` b " +
                    "left join `users` u on b.borrow_by = u.uid order by b.`create_time` desc limit ?,?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1,offset);
            psmt.setInt(2,limit);
            return this.warpBooks(psmt.executeQuery());
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }
    @Override
    public int listBooksCount() {
        try {
            String sql = "select count(*) as count from `books`";
            PreparedStatement psmt = conn.prepareStatement(sql);
            ResultSet resultSet = psmt.executeQuery();
            resultSet.next();
            return resultSet.getInt("count");
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    @Override
    public List<Book> listBorrowBooks(int uid, int limit, int offset) {
        try {
            String sql = "select *" +
                    "from `books` b " +
                    "left join `users` u on b.borrow_by = u.uid " +
                    "where b.`borrow_by` = ? " +
                    " order by b.`create_time` desc limit ?,?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1,uid);
            psmt.setInt(2,offset);
            psmt.setInt(3,limit);
            return this.warpBooks(psmt.executeQuery());
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    @Override
    public List<Book> listBookById(int bid, int limit, int offset) {
        try {
            String sql = "select *" +
                    "from `books` b " +
                    "left join `users` u on b.borrow_by = u.uid " +
                    "where b.`bid` = ? " +
                    " order by b.`create_time` desc limit ?,?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1,bid);
            psmt.setInt(2,offset);
            psmt.setInt(3,limit);
            return this.warpBooks(psmt.executeQuery());
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    @Override
    public int listBorrowBooksCount(int uid) {
        try {
            String sql = "select count(*) as count from `books` where `borrow_by` = ? ";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1,uid);
            ResultSet resultSet = psmt.executeQuery();
            resultSet.next();
            return resultSet.getInt("count");
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    /**
     * 读取字符串类型键值对 拼接出条件语句 并控制模糊查询或者精确查询
     *
     * @param conditions 要查询的条件的键值对
     * @param like       true 使用模糊查询 false 使用精确查询
     * @return
     */
    @Override
    public List<Book> listBooksByStringCondition(Map<String, String> conditions, boolean like, int limit, int offset) {
        StringBuilder sql = new StringBuilder();
        String baseSql = "select *" +
                "from `books` b " +
                "left join `users` u on b.borrow_by = u.uid ";
        sql.append(baseSql);
        PreparedStatement psmt = null;
        try {
            if (conditions != null) {
                sql.append("where ");
                List<Map.Entry<String, String>> entries = new ArrayList<>(conditions.entrySet());
                for (int i = 0; i < entries.size(); i++) {
                    // 拼接条件间的 and
                    if (i != 0) {
                        sql.append("and ");
                    }
                    // 使用 like 拼接
                    if (like) {
                        sql.append(entries.get(i).getKey()).append(" like ? ");
                    } else {
                        sql.append(entries.get(i).getKey()).append(" = ? ");
                    }
                }
                sql.append(" order by b.`create_time` desc limit ?,?");
                System.out.println(sql);
                psmt = conn.prepareStatement(String.valueOf(sql));
                for (int i = 0; i < entries.size(); i++) {
                    // 使用 like 拼接
                    if (like) {
                        psmt.setString(i + 1, "%" + entries.get(i).getValue() + "%");
                    } else {
                        psmt.setString(i + 1, entries.get(i).getValue());
                    }
                }
                psmt.setInt(entries.size()+1,offset);
                psmt.setInt(entries.size()+2,limit);
                return warpBooks(psmt.executeQuery());
            } else {
                sql.append(" order by b.`create_time` desc limit ?,?");
                psmt = conn.prepareStatement(String.valueOf(sql));
                return warpBooks(psmt.executeQuery());
            }
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    @Override
    public int listBooksByStringConditionCount(Map<String, String> conditions, boolean like) {
        StringBuilder sql = new StringBuilder();
        String baseSql = "select count(*) as count from `books`";
        sql.append(baseSql);
        PreparedStatement psmt = null;
        try {
            if (conditions != null) {
                sql.append("where ");
                List<Map.Entry<String, String>> entries = new ArrayList<>(conditions.entrySet());
                for (int i = 0; i < entries.size(); i++) {
                    // 拼接条件间的 and
                    if (i != 0) {
                        sql.append("and ");
                    }
                    // 使用 like 拼接
                    if (like) {
                        sql.append(entries.get(i).getKey()).append(" like ? ");
                    } else {
                        sql.append(entries.get(i).getKey()).append(" = ? ");
                    }
                }
                sql.append(" order by b.`create_time` desc limit ?,?");
                System.out.println(sql);
                psmt = conn.prepareStatement(String.valueOf(sql));
                for (int i = 0; i < entries.size(); i++) {
                    // 使用 like 拼接
                    if (like) {
                        psmt.setString(i + 1, "%" + entries.get(i).getValue() + "%");
                    } else {
                        psmt.setString(i + 1, entries.get(i).getValue());
                    }
                }
                ResultSet resultSet = psmt.executeQuery();
                resultSet.next();
                return resultSet.getInt("count");
            } else {
                psmt = conn.prepareStatement(String.valueOf(sql));
                ResultSet resultSet = psmt.executeQuery();
                resultSet.next();
                return resultSet.getInt("count");
            }
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    @Override
    public boolean updateBook(Book in) {
        try {
            PreparedStatement psmt = conn.prepareStatement("update `books` set " +
                    "`name` = ?, `author` = ?, `publisher` = ?, `description` = ?, " +
                    "`price` = ?, `position` = ?" +
                    "where `bid` = ?");
            psmt.setString(1, in.getName());
            psmt.setString(2, in.getAuthor());
            psmt.setString(3, in.getPublisher());
            psmt.setString(4, in.getDescription());
            psmt.setDouble(5, in.getPrice());
            psmt.setString(6, in.getPosition());
            psmt.setInt(7, in.getBid());
            int i = psmt.executeUpdate();
            if (i == 0) {
                throw new SQLException("==== updateBook 失败");
            } else {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updateBorrow(Book in) {
        try {
            PreparedStatement psmt = conn.prepareStatement("update `books` set `borrow_by` = ?, `expire_time` = ? " +
                    "where `bid` = ?");
            if (in.getBorrowBy()!=null){
                psmt.setInt(1,in.getBorrowBy().getUid());
                psmt.setTimestamp(2,new Timestamp(in.getExpireTime().getTime()));
            }else{
                psmt.setNull(1,Types.INTEGER);
                psmt.setNull(2,Types.TIMESTAMP);
            }
            psmt.setInt(3, in.getBid());
            int i = psmt.executeUpdate();
            if (i == 0) {
                throw new SQLException("==== UpdateBorrow 失败");
            } else {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean deleteBook(Book in) {
        try {
            PreparedStatement psmt = conn.prepareStatement("delete from `books` where `bid` = ?");
            psmt.setInt(1, in.getBid());
            int i = psmt.executeUpdate();
            if (i == 0) {
                throw new SQLException("==== deleteBook 失败");
            } else {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean addBook(Book in) {
        try {
            PreparedStatement psmt = conn.prepareStatement("insert into `books` (" +
                    "`name`,`author`,`publisher`,`description`,`price`,`position`" +
                    ") values (?,?,?,?,?,?)");
            psmt.setString(1,in.getName());
            psmt.setString(2,in.getAuthor());
            psmt.setString(3,in.getPublisher());
            psmt.setString(4,in.getDescription());
            psmt.setDouble(5,in.getPrice());
            psmt.setString(6,in.getPosition());
            psmt.executeUpdate();
            return true;
        } catch (SQLException throwables) {
            throw new MyException(throwables.getMessage());
        }
    }

    /**
     * 从结果集中组装 Book 列表
     * @param res
     * @return List<Book>
     * @throws SQLException
     */
    public List<Book> warpBooks(ResultSet res) throws SQLException {
        List<Book> books = new ArrayList<>();
        while (res.next()) {
            Book book = new Book();
            book.setBid(res.getInt("bid"));
            book.setName(res.getString("name"));
            book.setAuthor(res.getString("author"));
            book.setPublisher(res.getString("publisher"));
            book.setDescription(res.getString("description"));
            book.setPrice(res.getDouble("price"));
            book.setPosition(res.getString("position"));
            book.setCreateTime(res.getTimestamp("create_time"));
            if (StringUtil.notBlank(res.getString("borrow_by"))){
                book.setExpireTime(res.getTimestamp("expire_time"));
                book.setBorrowBy(UserDaoImpl.wrapUser(res));
            }
            books.add(book);
        }
        return books;
    }
}
